package jmine.tec.environment.db.server;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jmine.tec.persist.schema.api.SqlDialect;
import jmine.tec.persist.schema.impl.dialects.Oracle10gDialect;
import bancosys.tec.utils.db.converter.DbConverter;
import bancosys.tec.utils.db.converter.OracleConverter;
import bancosys.tec.utils.db.executor.DbExecutor;
import bancosys.tec.utils.db.executor.OracleDbExecutor;

/**
 * @author Rafael Volpato (May 11, 2007)
 */
public class OracleTestDBServer extends AbstractTestDBServer {

    /**
     * {@inheritDoc}
     */
    public void clearDBData(String... skipTables) {
        Connection conn = null;
        try {
            conn = this.getConnection();

            this.disableContraints(conn);
            this.truncateTable(conn);
            this.enableContraints(conn);

            conn.close();
        } catch (SQLException e1) {
            throw new OracleTestDBServerException("Não foi possível executar a limpeza do banco de dados", e1);
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * Ativas as constraints.
     * 
     * @param conn conexão com o banco.
     * @throws SQLException erro de sql.
     */
    private void enableContraints(Connection conn) throws SQLException {
        Statement sqlSt;
        sqlSt = conn.createStatement();
        ResultSet rs =
                sqlSt
                        .executeQuery("SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' ORDER BY TABLE_NAME");
        while (rs.next()) {
            String table = rs.getString(1);
            String constraint = rs.getString(2);

            Statement altSt = conn.createStatement();
            altSt.executeUpdate("ALTER TABLE " + table + " ENABLE CONSTRAINT " + constraint);
            altSt.close();
        }
        sqlSt.close();
    }

    /**
     * Trunca as tabelas.
     * 
     * @param conn conexão com o banco.
     * @throws SQLException erro de sql.
     */
    private void truncateTable(Connection conn) throws SQLException {
        List<String> tables = new ArrayList<String>();
        Statement sqlSt;
        tables = this.findTables(conn);

        for (String table : tables) {
            sqlSt = conn.createStatement();
            sqlSt.executeUpdate("TRUNCATE TABLE " + table);
            sqlSt.close();
        }
    }

    /**
     * Encontra as tabelas.
     * 
     * @param conn conexão
     * @return lista de tabelas
     * @throws SQLException erro de sql
     */
    private List<String> findTables(Connection conn) throws SQLException {
        List<String> foundTables = new ArrayList<String>();
        Statement sqlSt;
        sqlSt = conn.createStatement();
        ResultSet rs = sqlSt.executeQuery("SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME");
        while (rs.next()) {
            foundTables.add(rs.getString(1));
        }
        sqlSt.close();
        return foundTables;
    }

    /**
     * Encontra as sequences.
     * 
     * @param conn conexão
     * @return lista de tabelas
     * @throws SQLException erro de sql
     */
    private List<String> findSequences(Connection conn) throws SQLException {
        List<String> foundSequences = new ArrayList<String>();
        Statement sqlSt;
        sqlSt = conn.createStatement();
        ResultSet rs = sqlSt.executeQuery("SELECT SEQUENCE_NAME FROM USER_SEQUENCES");
        while (rs.next()) {
            foundSequences.add(rs.getString(1));
        }
        sqlSt.close();
        return foundSequences;
    }

    /**
     * Encontra as views.
     * 
     * @param conn conexão
     * @return lista de tabelas
     * @throws SQLException erro de sql
     */
    private List<String> findViews(Connection conn) throws SQLException {
        List<String> foundViews = new ArrayList<String>();
        Statement sqlSt;
        sqlSt = conn.createStatement();
        ResultSet rs = sqlSt.executeQuery("SELECT VIEW_NAME FROM USER_VIEWS");
        while (rs.next()) {
            foundViews.add(rs.getString(1));
        }
        sqlSt.close();
        return foundViews;
    }

    /**
     * Encontra as functions.
     * 
     * @param conn conexão
     * @return lista de tabelas
     * @throws SQLException erro de sql
     */
    private List<String> findFunctions(Connection conn) throws SQLException {
        List<String> foundFunctions = new ArrayList<String>();
        Statement sqlSt;
        sqlSt = conn.createStatement();
        ResultSet rs = sqlSt.executeQuery("SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION'");
        while (rs.next()) {
            foundFunctions.add(rs.getString(1));
        }
        sqlSt.close();
        return foundFunctions;
    }

    /**
     * Desativa as constraints.
     * 
     * @param conn conexão com o banco.
     */
    private void disableContraints(Connection conn) {
        Statement sqlSt;
        try {
            sqlSt = conn.createStatement();
            ResultSet rs =
                    sqlSt
                            .executeQuery("SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' ORDER BY TABLE_NAME");
            while (rs.next()) {
                String table = rs.getString(1);
                String constraint = rs.getString(2);

                Statement altSt = conn.createStatement();
                altSt.executeUpdate("ALTER TABLE " + table + " DISABLE CONSTRAINT " + constraint);
                altSt.close();
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new OracleTestDBServerException("Não foi possível pesquisar tabelas para serem removidas", e);
        }
    }

    /**
     * Exception
     * 
     * @created Oct 2, 2008
     * @author galmeida (Oct 2, 2008)
     */
    class OracleTestDBServerException extends RuntimeException {
        /**
         * Construtor.
         * 
         * @param message mensagem.
         * @param cause causa.
         */
        OracleTestDBServerException(String message, Throwable cause) {
            super(message, cause);
        }
    }

    /**
     * {@inheritDoc}
     */
    public void dropTables() throws SQLException {
        Connection conn = this.getConnection();

        List<String> tables = this.findTables(conn);
        for (String table : tables) {
            Statement sqlSt = conn.createStatement();
            sqlSt.executeUpdate("DROP TABLE " + table + " CASCADE CONSTRAINTS");
            sqlSt.close();
        }

        List<String> sequences = this.findSequences(conn);
        for (String sequence : sequences) {
            Statement sqlSt = conn.createStatement();
            sqlSt.executeUpdate("DROP SEQUENCE " + sequence);
            sqlSt.close();
        }

        List<String> views = this.findViews(conn);
        for (String view : views) {
            Statement sqlSt = conn.createStatement();
            sqlSt.executeUpdate("DROP VIEW " + view);
            sqlSt.close();
        }

        List<String> functions = this.findFunctions(conn);
        for (String function : functions) {
            Statement sqlSt = conn.createStatement();
            sqlSt.executeUpdate("DROP FUNCTION " + function);
            sqlSt.close();
        }
    }

    /**
     * @return O DbExecutor que deve ser utilizado oara este DbServer
     */
    public DbExecutor getDbExecutor() {
        return new OracleDbExecutor();
    }

    /**
     * @param inputFile O arquivo com o schema de criação do banco
     * @param outputFile O arquivo convertido
     * @param dbName O nome do banco
     * @return o DbConverter
     */
    public DbConverter getDbConverter(URL inputFile, File outputFile, String dbName) {
        return new OracleConverter(inputFile, outputFile, dbName);
    }

    /**
     * {@inheritDoc}
     */
    public SqlDialect getDialect() {
        return new Oracle10gDialect();
    }
}
